package com.shsh.safemanage.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import com.shsh.safemanage.model.JobPost;
import com.shsh.safemanage.model.JobTitle;
import com.shsh.safemanage.model.ManageGroup;
import com.shsh.safemanage.model.PostCheck;
import com.shsh.safemanage.model.Role;
import com.shsh.safemanage.model.User;
import com.shsh.safemanage.util.ValueUtil;

public class TitlePostManageGroupDao {
	public JobPost getJobPostByID(Long id) {
		if (id == null) {
			return null;
		}

		String sql = "select job_post.ID, DEPARTMENT1_ID, DEPARTMENT2_ID, POST_NAME, JOB_TITLE_ID, ROLE_TYPE, ROLE_ID "
		        + "from job_post left outer join role on job_post.ROLE_ID=role.ID where job_post.ID=?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, id);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				JobPost jobPost = new JobPost();
				jobPost.setId(rs.getLong("ID"));
				jobPost.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				jobPost.setDepartment2ID(rs.getLong("DEPARTMENT2_ID"));
				jobPost.setPostName(rs.getString("POST_NAME"));
				jobPost.setJobTitleID(rs.getInt("JOB_TITLE_ID"));
				jobPost.setRoleType(rs.getInt("ROLE_TYPE"));
				jobPost.setRoleID(rs.getLong("ROLE_ID"));

				return jobPost;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return null;
	}

	public List<JobPost> getJobPostByDepart1IDandDepart2ID(Long depart1ID, Long depart2ID) {
		if (depart1ID == null || depart2ID == null) {
			return null;
		}

		String sql = "select job_post.ID, DEPARTMENT1_ID, DEPARTMENT2_ID, POST_NAME, JOB_TITLE_ID, ROLE_TYPE, ROLE_ID "
		        + "from job_post left outer join role on job_post.ROLE_ID=role.ID where job_post.DEPARTMENT1_ID=? and job_post.DEPARTMENT2_ID=?";

		Connection conn = null;
		PreparedStatement pstm = null;
		List<JobPost> jobPostList = new ArrayList<JobPost>();
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, depart1ID);
			pstm.setLong(2, depart2ID);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				JobPost jobPost = new JobPost();
				jobPost.setId(rs.getLong("ID"));
				jobPost.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				jobPost.setDepartment2ID(rs.getLong("DEPARTMENT2_ID"));
				jobPost.setPostName(rs.getString("POST_NAME"));
				jobPost.setJobTitleID(rs.getInt("JOB_TITLE_ID"));
				jobPost.setRoleType(rs.getInt("ROLE_TYPE"));
				jobPost.setRoleID(rs.getLong("ROLE_ID"));

				jobPostList.add(jobPost);
			}
			return jobPostList;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return null;
	}

	public JobTitle getTitleByID(Long id) {
		String sql = "select ID, TITLE_TYPE, TITLE_LEVEL, TITLE_NAME from job_title where ID=?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, id);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				JobTitle jobTitle = new JobTitle();
				jobTitle.setId(rs.getLong("ID"));
				jobTitle.setTitleType(rs.getInt("TITLE_TYPE"));
				jobTitle.setTitleLevel(rs.getInt("TITLE_LEVEL"));
				jobTitle.setTitleName(rs.getString("TITLE_NAME"));

				return jobTitle;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return null;
	}

	public ManageGroup getManageGroup(Long id) {
		String sql = "select * from manage_group where id=?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, id);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				ManageGroup manageGroup = new ManageGroup();
				manageGroup.setId(ValueUtil.getLong(rs.getLong("ID")));
				manageGroup.setManageGroupName(ValueUtil.getString(rs.getString("MANAGE_GROUP_NAME")));

				return manageGroup;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return null;
	}

	public void setPostManageGroupRelation(Long postID, Long manageGroupID) {
		String sql = "update job_post set MANAGE_GROUP_ID=? where ID=?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			conn.setAutoCommit(false);

			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, manageGroupID);
			pstm.setLong(2, postID);

			pstm.executeUpdate();

			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public void setPostTitleRelation(Long postID, Long titleID) {
		String sql = "update job_post set JOB_TITLE_ID=? where ID=?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			conn.setAutoCommit(false);

			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, titleID);
			pstm.setLong(2, postID);

			pstm.executeUpdate();

			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public List<ManageGroup> getManageGroupList() {
		String sql = "select ID, MANAGE_GROUP_NAME from manage_group";
		List<ManageGroup> manageGroupList = new ArrayList<ManageGroup>();

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				ManageGroup manageGroup = new ManageGroup();
				manageGroup.setId(rs.getLong("ID"));
				manageGroup.setManageGroupName(rs.getString("MANAGE_GROUP_NAME"));

				manageGroupList.add(manageGroup);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}

		return manageGroupList;
	}

	public ManageGroup getManageGroupByID(Long id) {
		String sql = "select ID, MANAGE_GROUP_NAME from manage_group where ID=?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, id);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				ManageGroup manageGroup = new ManageGroup();
				manageGroup.setId(rs.getLong("ID"));
				manageGroup.setManageGroupName(rs.getString("MANAGE_GROUP_NAME"));

				return manageGroup;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}

		return null;
	}

	public Role getRoleByID(Long id) {
		String sql = "select ID, ROLE_TYPE, ROLE_NAME, ROLE_LEVEL from role where ID=?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, id);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				Role role = new Role();
				role.setId(rs.getLong("ID"));
				role.setRoleType(rs.getInt("ROLE_TYPE"));
				role.setRoleName(rs.getString("ROLE_NAME"));
				role.setRoleLevel(rs.getInt("ROLE_LEVEL"));

				return role;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return null;
	}

	public boolean addManagegroupPostcheckMapping(Long manageGroupID, Set<PostCheck> postcheckSet) {
		String selectSQL = "select ID from managegroup_postcheck_mapping where MANAGE_GROUP_ID=? and POSTCHECK_ID=?";
		String sql = "insert into managegroup_postcheck_mapping(MANAGE_GROUP_ID, POSTCHECK_ID) values(?, ?)";

		Connection conn = null;
		PreparedStatement pstm = null;
		PreparedStatement pstm1 = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(selectSQL);
			pstm1 = conn.prepareStatement(sql);
			for (PostCheck postcheck : postcheckSet) {
				pstm.setLong(1, manageGroupID);
				pstm.setLong(2, postcheck.getId());

				ResultSet rs = pstm.executeQuery();
				if (!rs.next()) {
					pstm1.setLong(1, manageGroupID);
					pstm1.setLong(2, postcheck.getId());

					pstm1.executeUpdate();
				}
			}

			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return false;
	}

	public boolean addUserManagegroupMapping(User user, Set<ManageGroup> managegroupSet) {
		String sql = "insert into user_managegroup_mapping(USER_ID, MANAGE_GROUP_ID, DEPARTMENT1_ID) values(?, ?, ?)";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, user.getId());
			for (ManageGroup manageGroup : managegroupSet) {
				pstm.setLong(2, manageGroup.getId());
				pstm.setLong(3, manageGroup.getDepart1ID());
				pstm.executeUpdate();
			}

			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return false;
	}
	
	public boolean deleteUserMappingManageGroups(Long userID) {
		String sql = "delete from user_managegroup_mapping where USER_ID=?";
		
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			
			pstm.setLong(1, userID);
			pstm.executeUpdate();
			
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return false;
	}

	public boolean updateJobPostandRoleMapping(Long jobPostID, Long roleID) {
		String sql = "update job_post set job_post.ROLE_ID = ? where job_post.ID = ?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			conn.setAutoCommit(false);
			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, roleID);
			pstm.setLong(2, jobPostID);

			pstm.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}

		return false;
	}
	
	public Set<ManageGroup> getUserMappingManageGroup(Long userID) {
		String sql = "select user_managegroup_mapping.DEPARTMENT1_ID, user_managegroup_mapping.MANAGE_GROUP_ID, MANAGE_GROUP_NAME " +
				"from user_managegroup_mapping inner join manage_group on user_managegroup_mapping.MANAGE_GROUP_ID=manage_group.ID " +
				"where USER_ID=?";
		
		Set<ManageGroup> manageGroupSet = new HashSet<ManageGroup>();
		
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, userID);
			
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				ManageGroup manageGroup = new ManageGroup();
				manageGroup.setId(rs.getLong("MANAGE_GROUP_ID"));
				manageGroup.setDepart1ID(rs.getLong("DEPARTMENT1_ID"));
				manageGroup.setManageGroupName(rs.getString("MANAGE_GROUP_NAME"));
				
				manageGroupSet.add(manageGroup);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return manageGroupSet;
	}
}
